package dbgroup02.dao.db2;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import dbgroup02.bean.Seat;
import dbgroup02.dao.SeatDao;

public class SeatDB2Dao implements SeatDao {

	private JdbcTemplate jdbcTemplate;

	ParameterizedRowMapper<Seat> seatMapper = new ParameterizedRowMapper<Seat>() {
		@Override
		public Seat mapRow(ResultSet resultSet, int arg1) throws SQLException {
			Seat seat = new Seat();
			seat.setSeat(resultSet.getString(1));
			return seat;
		}
	};

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	@Override
	@Transactional(readOnly = true, propagation = Propagation.REQUIRED)
	public List<Seat> getFreeSeats(int idFlight) {
		List<Seat> result = jdbcTemplate
				.query("select S.SEAT from "
						+ SCHEMA
						+ ".TOTAL_SEATS S	where S.SEAT not in	(select B.SEAT_NUMBER from "
						+ SCHEMA
						+ ".FLIGHT_TICKET F join "
						+ SCHEMA
						+ ".BOARDING_PASS2 B on B.BOARDING_PASS_ID = F.BOARDING_PASS_ID where F.FLIGHT_ID = ?)",
						new Object[] { idFlight }, seatMapper);

		return result;
	}

}
